﻿-- =============================================
-- Author:		<赵洪涛>
-- Create date: <2012-03-12>
-- Description:	<按指定的时间段、公司、账号取对应的存款数据>
-- =============================================
CREATE PROCEDURE [dbo].[proc_AB_GetList_In]
(
		@zhjb int,
		@InCompanyId varchar(500),
		@Iid int,
		@StartDate varchar(10),
		@EndDate varchar(10),
		@StartIndex int,
		@EndIndex int
)
AS
BEGIN
Declare @cSql varchar(2000)

Set @cSql = 'CompanyId In('+@InCompanyId+')'
If @Iid>0
	Set @cSql = @cSql+' And Iid='+Convert(varchar(10),@Iid)
IF @zhjb>0
	SET @cSql = @cSql+' And rootId='+Convert(varchar(10),@zhjb)
Set @cSql=';WITH list As(Select ROW_NUMBER() OVER (Order By Convert(varchar(10),UpTime,23) Desc,CompanyId,rootid,Iid)AS Row,
CompanyId,
(Select Top 1 CompanyName From [Company] Where [Company].Id=CompanyId) As Companyname,
Iid,
IsNull((Select Top 1 Productname From [AB_Interface] Where [AB_Interface].id=[AB_List].Iid),'''') As Productname,
je,
ye,
Person,
UpTime
From [AB_List]
Where '+@cSql+' And AB_Type=1 
And Convert(varchar(10),UpTime,23) between '''+@StartDate+''' and '''+@EndDate+'''
)

Select *,(Select Count(0) From list) As RecordCount From list Where Row Between '+Convert(varchar(10),@StartIndex)+' And '+Convert(varchar(10),@EndIndex)+' Order By Row'

--PRINT @cSql
--Select @cSql
Exec(@cSql)
END
